/*
 * Created on Jun 24, 2005
 *
 */
package com.erclab.internal.xpresso.util;

import com.erclab.internal.xpresso.conf.Settings;
import com.erclab.internal.xpresso.dataAccess.ApplicationDataSource;
import com.erclab.internal.xpresso.dataAccess.GenericResultSet;
import com.erclab.internal.xpresso.dataAccess.MysqlDatasource;
import com.erclab.internal.xpresso.dataAccess.Query;
import com.erclab.internal.xpresso.exceptions.DataSourceException;
import com.erclab.internal.xpresso.exceptions.ObjectCreatorException;

/**
 * @author Enrique Rodriguez.
 *
 */
public class MysqlObjectCreator implements ObjectCreator {

	private final String INSERT_FORM_QUERY = "INSERT INTO forms (FormName, Description, Title, Image, " +
					"forwardUrl, uniqueKeyName, viewHandler, parentKeyName, category, style) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private final String GET_IDFORM_QUERY = "SELECT IdForm FROM forms WHERE FormName=?";
	private final String INSERT_FORM_ELEMENT = "INSERT INTO formelements (IdForm, DisplayOrder, "
					+ " DestinationTable, DestinationColumn, DestinationDataSourceId, classHandler, "
                    + " Required, Label, DataType, Parameters) values(?,?,?,?,?,?,?,?,?,?)";
	private final String INSERT_REPORT_QUERY = "INSERT INTO reports (reportName, query, idDataSource, " +
					"title, description, image, uniqueKeyName, category, tableAssociated, " +
					" recordsPerPage, style, viewHandler, allowDelete, sortBy)" + 
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private final String GET_IDREPORT_QUERY = "SELECT idReport FROM reports WHERE reportName=?";
	private final String INSERT_REPORT_ELEMENT = "INSERT INTO reportelements (IdReport, DisplayOrder," +					" columnName, HeaderText, ClassHandler, Style, IsTotalColumn)" +					" values(?,?,?,?,?,?,?)";
	
	protected String DEFAULT_CATEGORY = "All";
	protected String DEFAULT_RECORDS_PER_PAGE = "20";
	protected String DEFAULT_STYLE = "Generic";
	protected String DEFAULT_REPORT_ELEMENT_CLASS_HANDLER = "com.erclab.internal.xpresso.reportelements.PrintValue";
	protected String DEFAULT_ALLOW_DELETE = "0";
	
	protected String FORM_FORWARD_URL = "-param:previousUrl-";
	protected String FORM_VIEW_HANDLER = "DynaForm.jsp";
	protected String REPORT_VIEW_HANDLER = "DynaReport.jsp";
	protected String FORM_ELEMENT_DEFAULT_TYPE = "com.erclab.xpresso.formelements.TextInputFI";
	
	protected int formElementOrder;
	protected int reportElementOrder;
	
	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.util.ObjectCreator#createForm(java.lang.String, java.lang.String, int, int)
	 */
	public void createForm(
		String objectName,
		String objectTarget,
		String uniqueKeyName,
		String parentKeyName,
		int idDatasourceRead,
		int idDatasourceWrite)
		throws ObjectCreatorException {
		
			// Create the form
			MysqlDatasource adsWrite = (MysqlDatasource)Settings.getRepository().getApplicationDataSource(idDatasourceWrite);
			MysqlDatasource adsRead = (MysqlDatasource)Settings.getRepository().getApplicationDataSource(idDatasourceRead);
			
			Query q = new Query();
			q.setQuery(INSERT_FORM_QUERY);
			q.setValues(new String[]{objectName, "TBD", objectName, "../html/images/defaultForm.gif",
									 FORM_FORWARD_URL, uniqueKeyName, FORM_VIEW_HANDLER, parentKeyName,
									  DEFAULT_CATEGORY, DEFAULT_STYLE});
			GenericResultSet gr = null;
			try {
				adsWrite.executeQuery(q);
				q.setQuery(GET_IDFORM_QUERY);
				q.setValues(new String[]{objectName});
				gr = adsWrite.runQuery(q);
			} catch (DataSourceException e) {
				throw new ObjectCreatorException("An exception ocurred creating a form:" + e);
			}
			
			gr.nextRow();
			String idForm = gr.getRow()[0];
			
			try {
				gr = adsRead.runQuery("DESCRIBE " + objectTarget);
			} catch (DataSourceException e) {
				throw new ObjectCreatorException("An exception ocurred creating a form:" + e);
			}
			
			while(gr.nextRow()){
				createFormElement(gr.getRow(), idForm, objectTarget, idDatasourceRead, adsWrite);
			}
		
	}


	/**
	 * @param strings
	 */
	public void createFormElement(String[] a, 
									String idForm, 
									String objectTarget, 
									int idDatasourceRead,
									MysqlDatasource ads) throws ObjectCreatorException {
		String field = a[0];
		String type = a[1];
		String required = a[2];
		String extra = a[5];
		
		if( ! extra.equals("auto_increment") && ! type.equals("timestamp")){

			Query q = new Query();
			q.setQuery(this.INSERT_FORM_ELEMENT);
		
			String[] values = new String[10];

			values[0] = idForm;
			values[1] = "" + formElementOrder;
			values[2] = objectTarget;
			values[3] = field;
			values[4] = "" + idDatasourceRead;
			values[5] = DataTypeDiscover.getFormElementClassHandler(type);
			values[6] = isRequired(required);
			values[7] = field;
			values[8] = FORM_ELEMENT_DEFAULT_TYPE;
			values[9] = "<x>120</x><y>" + formElementOrder*10 + "</y>";
			
			q.setValues(values);
			try {
				ads.executeQuery(q);
			} catch (DataSourceException e) {
				throw new ObjectCreatorException("An exception ocurred creating a form element:" + e);
			}
		
			formElementOrder = formElementOrder + 3;
		}
		
		
		
	}


	protected String isRequired(String required) {
		return ((required == null || required.equals("false") || required.equals("0"))? "0": "1");
	}


	/* (non-Javadoc)
	 * @see com.erclab.internal.xpresso.util.ObjectCreator#createReport(java.lang.String, java.lang.String, java.lang.String, int, int)
	 */
	public void createReport(
		String objectName,
		String objectTarget,
		String reportQuery,
		String uniqueKeyName,
		int idDatasourceRead,
		int idDatasourceWrite)
		throws ObjectCreatorException {
		
			// Create the report
			MysqlDatasource adsWrite = (MysqlDatasource)Settings.getRepository().getApplicationDataSource(idDatasourceWrite);
			MysqlDatasource adsRead = (MysqlDatasource)Settings.getRepository().getApplicationDataSource(idDatasourceRead);
			
			Query q = new Query();
			q.setQuery(INSERT_REPORT_QUERY);
			q.setValues(new String[]{objectName, reportQuery, "" + idDatasourceRead, objectName,
									"Description - " + objectName, "../html/images/defaultReport.gif",
									uniqueKeyName, DEFAULT_CATEGORY, objectTarget, DEFAULT_RECORDS_PER_PAGE,
									DEFAULT_STYLE, REPORT_VIEW_HANDLER, DEFAULT_ALLOW_DELETE, uniqueKeyName});
			
			GenericResultSet gr = null;
			try {
				adsWrite.executeQuery(q);
				q.setQuery(GET_IDREPORT_QUERY);
				q.setValues(new String[]{objectName});
				gr = adsWrite.runQuery(q);
			} catch (DataSourceException e) {
				throw new ObjectCreatorException("An exception ocurred creating a report:" + e);
			}

			gr.nextRow();
			String idReport = gr.getRow()[0];
			
			try {
				gr = adsRead.runQuery("SELECT * FROM (" + reportQuery + ") AS thequery LIMIT 1");
			} catch (DataSourceException e) {
				throw new ObjectCreatorException("An exception ocurred creating a form:" + e);
			}
			
			String[] columns = gr.getColumnNames();
			for (int i =0; i< columns.length; i++){
				createReportElement(columns[i], idReport, adsWrite);
			}
	}


	/**
	 * @param string
	 * @param idReport
	 */
	public void createReportElement(String columnName, String idReport, ApplicationDataSource ads) 
		throws ObjectCreatorException {
			
		Query q = new Query();
		q.setQuery(this.INSERT_REPORT_ELEMENT);
		q.setValues(new String[]{idReport, "" + reportElementOrder, columnName, columnName,
					DEFAULT_REPORT_ELEMENT_CLASS_HANDLER, DEFAULT_STYLE, "0"});

		try {
			ads.executeQuery(q);
		} catch (DataSourceException e) {
			throw new ObjectCreatorException("An exception ocurred creating a form element:" + e);
		}
		
		reportElementOrder = reportElementOrder + 3; 
		
	}

}
